import json
import os
from openpyxl import load_workbook
import pandas as pd
from tqdm import tqdm
from basic_component_related_management.models import *

# 创建一个superadmin，密码是superadmin1234
user_entity = User.objects.filter(user_account="superadmin").first()
if user_entity is None:
    user_entity = User.objects.create(
        user_account="superadmin",
        user_name="superadmin",
        password="C6IDQGUT42vVUX0neDCF2TB2kD42tBWwp4AGqp3DenmkTYI9urFbhJomuOMcI25De98AjQJPBT07AUW5oQdYeHfeQKr/uhR6OABnKu+/uof5viBguKPdDqkc/znPMjADdt66dxVi/yeur8EahDaUCvhssdB4HkJLxteCLGlEDZ/AJe1BiTBCgyN+7ZLCEjxXD4lKbI5mT9oWEQ+Pd1rE7fY/jmuDh6Nnz2YO7/0WvQA39KESBVzrVQ5bmEVSnUNkQqfzR8DkylHQ54swmNGXFog9xd7q0gjd2VrsBfE6qD4y+iJThQlALLh1f9QdVthAcuepLUKW+PI/mmyllQTufw=="
    )


def init_component_dataV1():
    
    # CompId	CompName	CompEName	IUPACName	Formula	CASNo
    # PubChemID	InChiID	InChiKey	Description	DSIID	SEIID1	SEIID2	SEIDBID	DDBID	DDBEID	DDBEAspen	AspenID	TRCID	OpenID	DipprID
    #
    # 构造很多个组分的属性
    PubChemID_entity = Attribute.objects.filter(
        main_dataIndex="system_PubChemID").first()
    if PubChemID_entity is None:
        PubChemID_entity = Attribute.objects.create(
            main_name="PubChemID",
            main_name_en="PubChemID",
            main_dataIndex="system_PubChemID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,PubChemID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    DSIID_entity = Attribute.objects.filter(
        main_dataIndex="system_DSIID").first()
    if DSIID_entity is None:
        DSIID_entity = Attribute.objects.create(
            main_name="DSIID",
            main_name_en="DSIID",
            main_dataIndex="system_DSIID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,DSIID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    SEIID_entity = Attribute.objects.filter(
        main_dataIndex="system_SEIID").first()
    if SEIID_entity is None:
        SEIID_entity = Attribute.objects.create(
            main_name="SEIID",
            main_name_en="SEIID",
            main_dataIndex="system_SEIID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,SEIID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    SEIDBID_entity = Attribute.objects.filter(
        main_dataIndex="system_SEIDBID").first()
    if SEIDBID_entity is None:
        SEIDBID_entity = Attribute.objects.create(
            main_name="SEIDBID",
            main_name_en="SEIDBID",
            main_dataIndex="system_SEIDBID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,SEIDBID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    DDBID_entity = Attribute.objects.filter(
        main_dataIndex="system_DDBID").first()
    if DDBID_entity is None:
        DDBID_entity = Attribute.objects.create(
            main_name="DDBID",
            main_name_en="DDBID",
            main_dataIndex="system_DDBID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,DDBID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    # DDBEAspen	AspenID	TRCID	OpenID	DipprID

    DDBEAspen_entity = Attribute.objects.filter(
        main_dataIndex="system_DDBEAspen").first()
    if DDBEAspen_entity is None:
        DDBEAspen_entity = Attribute.objects.create(
            main_name="DDBEAspen",
            main_name_en="DDBEAspen",
            main_dataIndex="system_DDBEAspen",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,DDBEAspen",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    AspenID_entity = Attribute.objects.filter(
        main_dataIndex="system_AspenID").first()
    if AspenID_entity is None:
        AspenID_entity = Attribute.objects.create(
            main_name="AspenID",
            main_name_en="AspenID",
            main_dataIndex="system_AspenID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,AspenID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    TRCID_entity = Attribute.objects.filter(
        main_dataIndex="system_TRCID").first()
    if TRCID_entity is None:
        TRCID_entity = Attribute.objects.create(
            main_name="TRCID",
            main_name_en="TRCID",
            main_dataIndex="system_TRCID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,TRCID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    OpenID_entity = Attribute.objects.filter(
        main_dataIndex="system_OpenID").first()
    if OpenID_entity is None:
        OpenID_entity = Attribute.objects.create(
            main_name="OpenID",
            main_name_en="OpenID",
            main_dataIndex="system_OpenID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,OpenID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    DipprID_entity = Attribute.objects.filter(
        main_dataIndex="system_DipprID").first()
    if DipprID_entity is None:
        DipprID_entity = Attribute.objects.create(
            main_name="DipprID",
            main_name_en="DipprID",
            main_dataIndex="system_DipprID",
            main_datatype="short_string",
            main_necessary=False,
            common_used=True,
            comment="系统初始化,DipprID",
            is_system_config=True,
            create_person=user_entity,
            modify_person=user_entity,
        )

    # 用于批量插入的列表
    component_identifiers_to_create = []
    component_attribute_values_to_create = []
    data_file = "files/component_dataV1/AllComponent.xlsx"
    sheet_name1 = "Sheet1"
    df1 = pd.read_excel(data_file, sheet_name=sheet_name1, dtype=str)
    df1 = df1.fillna("")
    # 遍历 DataFrame
    for index, row in tqdm(df1.iterrows(), total=df1.shape[0], desc="Processing rows"):
        IUPACName = row["IUPACName"]
        Formula = row["Formula"]
        CASNo = row["CASNo"]
        InChiKey = row["InChiKey"]

        # 确定主名称
        primary_name = IUPACName if IUPACName else Formula if Formula else CASNo if CASNo else InChiKey

        # 创建 ComponentIdentifier 对象
        component_identify1 = ComponentIdentifier(
            name=primary_name,
            name_en=primary_name,
            molecular_formula=Formula,
            cas_number=CASNo,
            inchi_number=InChiKey,
            create_person=user_entity,
            modify_person=user_entity,
        )
        component_identifiers_to_create.append(component_identify1)

        # 属性内容
        attribute_values = [
            ("PubChemID", row["PubChemID"], PubChemID_entity),
            ("DSIID", row["DSIID"], DSIID_entity),
            ("SEIID1", row["SEIID1"], SEIID_entity),
            ("SEIDBID", row["SEIDBID"], SEIDBID_entity),
            ("DDBID", row["DDBID"], DDBID_entity),
            ("DDBEAspen", row["DDBEAspen"], DDBEAspen_entity),
            ("AspenID", row["AspenID"], AspenID_entity),
            ("TRCID", row["TRCID"], TRCID_entity),
            ("OpenID", row["OpenID"], OpenID_entity),
            ("DipprID", row["DipprID"], DipprID_entity),
        ]
        # print(attribute_values)
        # 创建 ComponentAttributeValue 对象
        for attribute_name, value, attribute_entity in attribute_values:
            if value:  # 只有当属性有值时才创建 ComponentAttributeValue
                component_attribute_value = ComponentAttributeValue(
                    componentidentifier=component_identify1,
                    attribute=attribute_entity,
                    main_value=json.dumps(value),
                    extend_value="",
                )
                component_attribute_values_to_create.append(
                    component_attribute_value)

        # 每1000条进行一次批量插入
        if len(component_identifiers_to_create) >= 1000:
            ComponentIdentifier.objects.bulk_create(
                component_identifiers_to_create)
            ComponentAttributeValue.objects.bulk_create(
                component_attribute_values_to_create)

            # 清空列表以准备下一批次
            component_identifiers_to_create.clear()
            component_attribute_values_to_create.clear()
        # if index > 10:
        #     break

    # 插入剩余数据
    if component_identifiers_to_create:
        ComponentIdentifier.objects.bulk_create(component_identifiers_to_create)
    if component_attribute_values_to_create:
        ComponentAttributeValue.objects.bulk_create(
            component_attribute_values_to_create)
